Skip Headers

Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALL_TAB_SUBPARTITIONS

`ALL_TAB_SUBPARTITIONS` describes, for each table subpartition accessible to the current user, the subpartition name, name of the table and partition to which it belongs, and its storage attributes.

Related Views

- `DBA_TAB_SUBPARTITIONS` provides such information for all subpartitions in the database.

- `USER_TAB_SUBPARTITIONS` provides such information for subpartitions of all partitioned objects owned by the current user. This view does not display the `TABLE_OWNER` column.

Note: Statistics are not collected on a per-subpartition basis.

Column | Datatype | NULL | Description
--- | --- | --- | ---
`TABLE_OWNER` | `VARCHAR2(30)` | `NOT NULL` | Table owner
`TABLE_NAME` | `VARCHAR2(30)` | `NOT NULL` | Table name
`PARTITION_NAME` | `VARCHAR2(30)` |  | Partition name
`SUBPARTITION_NAME` | `VARCHAR2(30)` |  | Subpartition name
`SUBPARTITION_POSITION` | `NUMBER` | `NOT NULL` | Position of a subpartition within a partition
`TABLESPACE_NAME` | `VARCHAR2(30)` | `NOT NULL` | Name of the tablespace containing the subpartition
`PCT_FREE` | `NUMBER` | `NOT NULL` | Minimum percentage of free space in a block
`PCT_USED` | `NUMBER` | `NOT NULL` | Minimum percentage of used space in a block
`INI_TRANS` | `NUMBER` | `NOT NULL` | Initial number of transactions
`MAX_TRANS` | `NUMBER` | `NOT NULL` | Maximum number of transactions
`INITIAL_EXTENT` | `NUMBER` |  | Size of the initial extent in bytes (for a range partition); size of the initial extent in blocks (for a composite partition)
`NEXT_EXTENT` | `NUMBER` |  | Size of secondary extents in bytes (for a range partition); size of secondary extents in blocks (for a composite partition)
`MIN_EXTENT` | `NUMBER` | `NOT NULL` | Minimum number of extents allowed in the segment
`MAX_EXTENT` | `NUMBER` | `NOT NULL` | Maximum number of extents allowed in the segment
`PCT_INCREASE` | `NUMBER` | `NOT NULL` | Percentage increase in extent size
`FREELISTS` | `NUMBER` |  | Number of freelist groups allocated in this segment
`FREELIST_GROUPS` | `NUMBER` |  | Number of freelist groups allocated in this segment
`LOGGING` | `VARCHAR2(3)` |  | Logging attribute of subpartition
`NUM_ROWS` | `NUMBER` |  | The number of rows
`BLOCKS` | `NUMBER` |  | The number of blocks
`EMPTY_BLOCKS` | `NUMBER` |  | The number of empty blocks
`AVG_SPACE` | `NUMBER` |  | The average space
`CHAIN_CNT` | `NUMBER` |  | The chain count
`AVG_ROW_LEN` | `NUMBER` |  | The average row length
`SAMPLE_SIZE` | `NUMBER` |  | The sample size
`LAST_ANALYZED` | `DATE` |  | The date on which this table was most recently analyzed
`BUFFER_POOL` | `VARCHAR2(7)` |  | The default buffer pool for this subpartition
`GLOBAL_STATS` | `VARCHAR2(3)` |  | Indicates whether column statistics for the subpartition were collected by analyzing the table as a whole (`YES`) or estimated from statistics collected for partitions and subpartitions (`NO`)
`USER_STATS` | `VARCHAR2(3)` |  | Indicates whether statistics were entered directly by the user (`YES`) or not (`NO`)